var DATABASE_NAME = 'POS';

exports.createDb = function() {
	Ti.Database.install('POS.sqlite', DATABASE_NAME);
};

exports.selectTableScreens = function() {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);

	var rows = db.execute('select * from TableScreens order by ROWID asc');
	while (rows.isValidRow()) {
		retData.push({id:rows.fieldByName('id'), Name:rows.fieldByName('Name')});
		rows.next();
	}
	db.close();
	return retData;
	
};

exports.selectTableScreenTables = function(TableScreen_Id) {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);

	var rows = db.execute('select TableScreen_Id,Table_Id,Name,TicketId,IsTicketLocked from TableScreenTables a,Tables b where a.Table_Id=b.Id and TableScreen_Id=?',TableScreen_Id);
	while (rows.isValidRow()) {
		retData.push({
			TableScreen_Id:rows.fieldByName('TableScreen_Id'), 
			Table_Id:rows.fieldByName('Table_Id'),
			Name:rows.fieldByName('Name'),
			TicketId:rows.fieldByName('TicketId'),
			IsTicketLocked:rows.fieldByName('IsTicketLocked')
		});
		rows.next();
	}
	db.close();
	return retData;
	
};

exports.selectScreenMenuCategories = function() {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	
	var rows = db.execute('select  * from ScreenMenuCategories');
	while (rows.isValidRow()) {
		retData.push({
			Id:rows.fieldByName('Id'), 
			Name:rows.fieldByName('Name')
		});
		rows.next();
	}
	db.close();
	return retData;
	
};

exports.selectScreenMenuItems = function(ScreenMenuCategoryId) {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	
	
	var rows =db.execute(
		'SELECT f.Id,f.MenuItemId,f.Name as "MenuItemName",f.ScreenMenuCategoryId,b.Price_Amount '+
		',b.Price_CurrencyCode as "CurrencyCode",b.Name as "PortionName"'+
		'FROM ScreenMenuItems f left join	'+
		'MenuItems a on a.Id=f.MenuItemId	'+
		'left join MenuItemPortions b on a.Id=b.MenuItemId	where ScreenMenuCategoryId=?',ScreenMenuCategoryId);
	while (rows.isValidRow()) {
		retData.push({
			Id:rows.fieldByName('Id'),
			Name:rows.fieldByName('MenuItemName'),
			ScreenMenuCategoryId:rows.fieldByName('ScreenMenuCategoryId'),
			Price_Amount:rows.fieldByName('Price_Amount')
			
			
			
			,MenuItemId:rows.fieldByName('MenuItemId')//
			,MenuItemName:rows.fieldByName('MenuItemName')//
			,PortionName:rows.fieldByName('PortionName')//
			,Price:rows.fieldByName('Price_Amount')//
			,CurrencyCode:rows.fieldByName('CurrencyCode')//
			/*
			,Quantity:rows.fieldByName('Quantity')
			,PortionCount:rows.fieldByName('PortionCount')
			,Locked:rows.fieldByName('Locked')
			,Voided:rows.fieldByName('Voided')
			,ReasonId:rows.fieldByName('ReasonId')
			,Gifted:rows.fieldByName('Gifted')
			,OrderNumber:rows.fieldByName('OrderNumber')
			,CreatingUserId:rows.fieldByName('CreatingUserId')
			,CreatedDateTime:rows.fieldByName('CreatedDateTime')
			,ModifiedUserId:rows.fieldByName('ModifiedUserId')
			,ModifiedDateTime:rows.fieldByName('ModifiedDateTime')
			,PriceTag:rows.fieldByName('PriceTag')
			,Tag:rows.fieldByName('Tag')
			,DepartmentId:rows.fieldByName('DepartmentId')
			,VatRate:rows.fieldByName('VatRate')
			,VatAmount:rows.fieldByName('VatAmount')
			,VatTemplateId:rows.fieldByName('VatTemplateId')
			,VatIncluded:rows.fieldByName('VatIncluded')
			*/
			
			
			
		});
		rows.next();
	}
	db.close();
	return retData;
	
};

exports.selectTicketItems = function(TicketId) {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);

	var rows = db.execute('select MenuItemName as Name,Id,TicketId,MenuItemId,Price as Price_Amount,Quantity from TicketItems where TicketId=?',TicketId);
	while (rows.isValidRow()) {
		retData.push({
			TicketId:rows.fieldByName('TicketId'), 
			Id:rows.fieldByName('Id'),
			MenuItemId:rows.fieldByName('MenuItemId'),
			Name:rows.fieldByName('Name'),
			Price_Amount:rows.fieldByName('Price_Amount'),
			Quantity:rows.fieldByName('Quantity'),
		});
		
		rows.next();
	}
	db.close();
	return retData;
};
exports.selectTickets = function(TicketId) {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	var rows = db.execute('select * from Tickets where Id=?',TicketId);
	while (rows.isValidRow()) {
		retData.push({
			Id:rows.fieldByName('Id'),
			Name:rows.fieldByName('Name'),
			Note:rows.fieldByName('Note'),
			TotalAmount:rows.fieldByName('TotalAmount'),
			RemainingAmount:rows.fieldByName('RemainingAmount'),
		});
		rows.next();
	}
	db.close();
	return retData;
};

exports.selectTables = function(TablesId) {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	var rows = db.execute('Select  Id,Name,TicketId,IsTicketLocked from Tables where Id=?',TablesId);
	while (rows.isValidRow()) {
		retData.push({
			Id:rows.fieldByName('Id'),
			Name:rows.fieldByName('Name'),
			TicketId:rows.fieldByName('TicketId'),
			IsTicketLocked:rows.fieldByName('IsTicketLocked'),
		});
		rows.next();
	}
	db.close();
	return retData;
};

exports.selectId = function(TablesName) {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	var rows = db.execute('SELECT max(Id) as "Id" from '+TablesName);
	while (rows.isValidRow()) {
		retData.push({
			Id:rows.fieldByName('Id'),
		});
		rows.next();
	}
	db.close();
	return retData;
};


exports.insertTicket_Table = function(Tables,Item,Quantity) {
	
		var getNow=require('cs/util').getNow();
		Ti.API.info ('getNow:'+getNow);
		//var Tickets=require('db').selectRowId('Tickets')
		//Ti.API.info ('TicketsRowId:'+Tickets[0].rowid);
		
		var mydb = Ti.Database.open(DATABASE_NAME);
		var sql='insert into [Tickets]( '+
				'[Id],  '+
				'[Name],  '+
				'[DepartmentId],  '+
				'[LastUpdateTime],  '+
				'[TicketNumber],  '+
				'[PrintJobData],  '+
				'[Date],  '+
				'[LastOrderDate],  '+
				'[LastPaymentDate], '+
				'[LocationName],  '+
				'[CustomerId], '+
				'[CustomerName],  '+
				'[CustomerGroupCode],  '+
				'[IsPaid],  '+
				'[RemainingAmount], '+
				'[TotalAmount],  '+
				'[Note],  '+
				'[Locked],  '+
				'[Tag] '+
				
				') values ( '+
				
				'(SELECT max(Id)+1 FROM Tickets)'+//'[Id],  '+
				',?'+//'[Name],  '+
				',?'+//'[DepartmentId],  '+
				',?'+//'[LastUpdateTime],  '+
				',(SELECT max(TicketNumber)+1 FROM Tickets)'+//'[TicketNumber],  '+
				',?'+//'[PrintJobData],  '+
				',?'+//'[Date],  '+
				',?'+//'[LastOrderDate],  '+
				',?'+//'[LastPaymentDate], '+
				',?'+//'[LocationName],  '+
				',?'+//'[CustomerId], '+
				',?'+//'[CustomerName],  '+
				',?'+//'[CustomerGroupCode],  '+
				',?'+//'[IsPaid],  '+
				',?'+//'[RemainingAmount], '+
				',?'+//'[TotalAmount],  '+
				',?'+//'[Note],  '+
				',?'+//'[Locked],  '+
				',?)';//'[Tag] '+
		Ti.API.info ('sql:'+sql);	
		mydb.execute(sql
				,'null'//'[Name],  '
				,1//'[DepartmentId],  '
				,getNow//'[LastUpdateTime],  '
				,'null'//'[PrintJobData],  '
				,getNow//'[Date],  '
				,getNow//'[LastOrderDate],  '
				,getNow//'[LastPaymentDate], '
				,Tables[0].Name//'[LocationName],  '
				,0//'[CustomerId], '
				,''//'[CustomerName],  '
				,''//'[CustomerGroupCode],  '
				,0//'[IsPaid],  '
				,Item.Price*Quantity//'[RemainingAmount], '
				,Item.Price*Quantity//'[TotalAmount],  '
				,'null'//'[Note],  '
				,0//'[Locked],  '
				,'123');//'[Tag] '
		mydb.close();
		return require('db').selectId('Tickets')[0].Id
}
exports.updateTickets_Table = function(TicketId,Item,Quantity) {
	
	var getNow=require('cs/util').getNow();
	var mydb = Ti.Database.open(DATABASE_NAME);
	var sql='update [Tickets] '+
			'set [LastUpdateTime] = '+getNow+', [LastOrderDate] = '+getNow+', [RemainingAmount] = RemainingAmount+?, [TotalAmount] = TotalAmount+? '+
			'where [Id] = ?';
	var RemainingAmount=Item.Price*Quantity
	var TotalAmount=Item.Price*Quantity
	mydb.execute(sql,RemainingAmount,TotalAmount,TicketId);
	mydb.close();
	Ti.API.info ('RemainingAmount:'+RemainingAmount);
}

exports.updateTables_Table = function(TicketId,TablesId) {
	
	var mydb = Ti.Database.open(DATABASE_NAME);
	var sql='update [Tables] set [TicketId] = ? where [Id] = ?'
	mydb.execute(sql,TicketId,TablesId);
	mydb.close();
}
exports.insertTicketItems_Table = function(TicketId,Item,Quantity) {
	var getNow=require('cs/util').getNow();
	var mydb = Ti.Database.open(DATABASE_NAME);
	var sql='insert into [TicketItems]( '+
		    '[Id],  '+
			'[TicketId],  '+
			'[MenuItemId],  '+
			'[MenuItemName],  '+
			'[PortionName],  '+
			'[Price],  '+
			'[CurrencyCode],  '+
			'[Quantity],  '+
			'[PortionCount],  '+
			'[Locked],  '+
			'[Voided],  '+
			'[ReasonId],  '+
			'[Gifted],  '+
			'[OrderNumber],  '+
			'[CreatingUserId],  '+
			'[CreatedDateTime],  '+
			'[ModifiedUserId],  '+
			'[ModifiedDateTime],  '+
			'[PriceTag],  '+
			'[Tag],  '+
			'[DepartmentId],  '+
			'[VatRate],  '+
			'[VatAmount],  '+
			'[VatTemplateId],  '+
			'[VatIncluded])  '+
			' values  ('+
			'(SELECT max(Id)+1 FROM TicketItems)'+//'[Id],  '+
			',?'+//'[TicketId],  '+
			',?'+//'[MenuItemId],  '+
			',?'+//'[MenuItemName],  '+
			',?'+//'[PortionName],  '+
			',?'+//'[Price],  '+
			',?'+//'[CurrencyCode],  '+
			',?'+//'[Quantity],  '+
			',?'+//'[PortionCount],  '+
			',?'+//'[Locked],  '+
			',?'+//'[Voided],  '+
			',?'+//'[ReasonId],  '+
			',?'+//'[Gifted],  '+
			',?'+//'[OrderNumber],  '+
			',?'+//'[CreatingUserId],  '+
			',?'+//'[CreatedDateTime],  '+
			',?'+//'[ModifiedUserId],  '+
			',?'+//'[ModifiedDateTime],  '+
			',?'+//'[PriceTag],  '+
			',?'+//'[Tag],  '+
			',?'+//'[DepartmentId],  '+
			',?'+//'[VatRate],  '+
			',?'+//'[VatAmount],  '+
			',?'+//'[VatTemplateId],  '+
			',?'+//'[VatIncluded])  '+
			')';
	mydb.execute(sql,
			
			TicketId//'[TicketId],  '+
			,Item.MenuItemId//'[MenuItemId],  '+
			,Item.MenuItemName//'[MenuItemName],  '+
			,Item.PortionName//'[PortionName],  '+
			,Item.Price//'[Price],  '+
			,Item.CurrencyCode//'[CurrencyCode],  '+
			,Quantity//'[Quantity],  '+
			,1//'[PortionCount],  '+
			,0//'[Locked],  '+
			,0//'[Voided],  '+
			,0//'[ReasonId],  '+
			,0//'[Gifted],  '+
			,1//'[OrderNumber],  '+
			,1//'[CreatingUserId],  '+
			,getNow//'[CreatedDateTime],  '+
			,0//'[ModifiedUserId],  '+
			,getNow//'[ModifiedDateTime],  '+
			,''//'[PriceTag],  '+
			,''//'[Tag],  '+
			,Item.DepartmentId//'[DepartmentId],  '+
			,0//'[VatRate],  '+
			,0//'[VatAmount],  '+
			,0//'[VatTemplateId],  '+
			,0//'[VatIncluded])  '+
	);
	mydb.close();
	Ti.API.info ('Thanh cong:');
}



exports.insertTicketItems = function(TablesId,Item,Quantity) {
	Ti.API.info ('Item tra ve:'+Item);
	//Kiem tra da tao ban chua,neu chua thi tao phieu (TicketID)
	var Tables=require('db').selectTables(TablesId)
	var TicketId=Tables[0].TicketId
    //Ti.API.info ('TicketId:'+TicketId);
		
	if(TicketId==0)
	{
		//Chua tao ban,khoi tao phieu
	
		TicketId=require('db').insertTicket_Table(Tables,Item,Quantity)
		Ti.API.info ('TicketId tra ve:'+TicketId);
		//Update Tables
		require('db').updateTables_Table(TicketId,Tables[0].Id)
		//Insert TicketItems
		require('db').insertTicketItems_Table(TicketId,Item,Quantity)
	}
	else
	{
		//Da co phieu (ban da tao)
		//Insert TicketItems
		require('db').insertTicketItems_Table(TicketId,Item,Quantity)
		//Update Ticket
		require('db').updateTickets_Table(TicketId,Item,Quantity)
		
	}
	
};



////////////////////////////////////CHUYEN BAN/////////////////////////////////////////////////
exports.ChangeTable_UpdateTable = function(TableId_Old,TableId_New,TableName_Old,TableName_New,TicketId) {
	var mydb = Ti.Database.open(DATABASE_NAME);
	//update ban moi
	var sql1='update [Tables] set [TicketId] = ? where [Id] = ?'
	mydb.execute(sql1, TicketId, TableId_New);
	//Xoa ban cu
	var sql2='update [Tables] set [TicketId] = 0 where [Id] = ?'
	mydb.execute(sql2, TableId_Old);
	//Update Ticket
	var getNow=require('cs/util').getNow();
	var sql3='update [Tickets] set [LastUpdateTime] = ?, [LocationName] = ? where [Id] = ?'
	mydb.execute(sql3, getNow,TableName_New,TicketId);
	mydb.close();
	return TicketId
}
exports.ChangeTable_UpdateTable_Bandaco = function(TableId_Old,TableId_New,TableName_Old,TableName_New,TicketId_Old,TicketId_New) {
	var mydb = Ti.Database.open(DATABASE_NAME);
	var getNow=require('cs/util').getNow();
	//Xoa ban cu
	var sql1='update [Tables] set [TicketId] = 0 where [Id] = ?'
	mydb.execute(sql1, TableId_Old);
	//Update Ticket cu
	var sql2='update [Tickets] set '+
			'[LastUpdateTime] = ?,  '+
			'[IsPaid] = ?,  '+
			'[RemainingAmount] = ?,  '+
			'[TotalAmount] = ?, '+
			'[Note] = ? '+
			'where [Id] = ?';
		
	mydb.execute(sql2, getNow,1,0,0,TableName_Old+' => '+TableName_New+' - ',TicketId_Old);

	//Chuyen cac TicketItems cu sang Ticket moi
	var sql3='update [TicketItems] set TicketId=? where TicketId=?';

	mydb.execute(sql3, TicketId_New,TicketId_Old);
    mydb.close();
    return TicketId_New
}


exports.ChangeTable = function(TableId_Old,TableId_New,TableName_Old,TableName_New) {
	//Ti.API.info ('Item tra ve:'+Item);
	
	//Kiem tra ban chuyen toi la ban moi hay la ban cu
	var Tables=require('db').selectTables(TableId_New)
	var TicketId_New=Tables[0].TicketId
	if(TicketId_New==0)
	{
		//Ban moi
		//Update Table 
		Ti.API.info ('chuyen sang Ban moi');
		var Tables_Old=require('db').selectTables(TableId_Old)
		var TicketId_Old=Tables_Old[0].TicketId
		var TicketId=require('db').ChangeTable_UpdateTable(TableId_Old,TableId_New,TableName_Old,TableName_New,TicketId_Old)
		return TicketId
	}
	else
	{
		//Ban cu,dang co nguoi ngoi
		Ti.API.info ('chuyen sang Ban cu');
		var Tables_Old=require('db').selectTables(TableId_Old)
		var TicketId_Old=Tables_Old[0].TicketId
		var TicketId=require('db').ChangeTable_UpdateTable_Bandaco(TableId_Old,TableId_New,TableName_Old,TableName_New,TicketId_Old,TicketId_New)
		return TicketId
	}
	
};










